﻿using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.Linq;
using System.Text;
using CommunitySystemModel;
using System.Data.SqlClient;

namespace CommunitySystem.BO
{
    public class JinShengBO
    {

        public bool SaveJinShen_MainInfor(JinShen_MainInfo entity)
        {
            try
            {
                if (entity.ID > 0)
                {
                    string sql = "delete JinShen_DetailInfo where MainID=" + entity.ID;
                    AccessHelper.GetInstance().ExecuteNonQuery(sql);
                    sql = "delete JinShen_MainInfo_Attatchment where MainID=" + entity.ID;
                    AccessHelper.GetInstance().ExecuteNonQuery(sql);
                }
                int id = AccessHelper.GetInstance().SaveEntity(entity, "", new List<string>() { "Details", "Attachment" });

                if (entity.Details != null)
                {
                    foreach (var detail in entity.Details)
                    {
                        detail.ID = 0;
                        detail.MainID = id;
                        AccessHelper.GetInstance().SaveEntity(detail, "", null);
                    }
                }
                if (entity.Attachment != null)
                {
                    foreach (var attachment in entity.Attachment)
                    {
                        attachment.ID = 0;
                        attachment.MainID = id;
                        AccessHelper.GetInstance().SaveEntity(attachment, "", null);
                    }
                }
            }
            catch (Exception ex)
            {
                return false;
            }
            return true;
        }

        public ObservableCollection<JinShen_MainInfo> GetJinshenMainInfo(string condition, string LoginUser)
        {
            string sql = string.Format(@"select r.RoleName from UserInfo u
left join Roles r on r.id = u.roleID
where u.loginname='{0}'", LoginUser);
            List<string> SuperAdmin = new List<string>()
            {
                "管理员",
                "系统操作"
            };

            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            string role = ds.Tables[0].Rows[0][0] as string;
            if (!SuperAdmin.Contains(role))
            {
                if (string.IsNullOrEmpty(condition))
                {
                    condition = string.Format("CreateUser = {0}", LoginUser);
                }
                else
                {
                    condition += string.Format("and CreateUser = '{0}'", LoginUser);
                }
            }
            var mains = AccessHelper.GetInstance().GetEnity<JinShen_MainInfo>(condition, "");
            foreach (var main in mains)
            {
                main.Details = AccessHelper.GetInstance().GetEnity<JinShen_DetailInfo>("MainID=" + main.ID, "");
                main.Attachment = AccessHelper.GetInstance().GetEnity<JinShen_MainInfo_Attatchment>("MainID=" + main.ID, "");
            }

            return mains;
        }

        public ObservableCollection<Emp_BasicInfoModel> GetEmpBasicInfo(string Condition)
        {
            return AccessHelper.GetInstance().GetEnity<Emp_BasicInfoModel>(Condition, "Emp_BasicInfo");
        }

        public ObservableCollection<Emp_BasicInfoModel> GetEmpBasicInfoAccordingToDuty(string condition)
        {
            StringBuilder sb = new StringBuilder(@"SELECT emp.Name, emp.ID, emp.CardNo FROM dbo.Emp_BasicInfo emp
INNER JOIN dbo.Emp_Duty duty ON emp.ID = duty.EmpbasicID  AND duty.JobStatus = '在任'");
            ObservableCollection<Emp_BasicInfoModel> list = new ObservableCollection<Emp_BasicInfoModel>();
            if (!string.IsNullOrEmpty(condition))
            {
                sb.Append("    where   ");
                sb.Append(condition);
            }
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sb.ToString());
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    Emp_BasicInfoModel emp = new Emp_BasicInfoModel();
                    emp.ID = Convert.ToInt32(dr["ID"]);
                    emp.Name = Convert.ToString(dr["Name"]);
                    emp.CardNo = Convert.ToString(dr["CardNo"]);
                    list.Add(emp);
                }
            }
            return list;
        }

        public string GetOrgForHR(string loginUser)
        {
            string sql = string.Format(@"select r.RoleName from UserInfo u 
left join Roles r on r.id = u.roleID
where u.loginname='{0}'", loginUser);
            DataSet ds = AccessHelper.GetInstance().ExcuteDataSet(sql);
            return ds.Tables[0].Rows[0][0] as string;
        }

        public bool SaveJinShen_ChooseCandidator_Main(JinShen_ChooseCandidator_Main entity)
        {
            try
            {
                if (entity.ID > 0)
                {
                    string sqlDeleteDetail = "delete JinShen_ChooseCandidator_Detail where ChooseMainID=" + entity.ID;
                    AccessHelper.GetInstance().ExecuteNonQuery(sqlDeleteDetail);
                    AccessHelper.GetInstance().SaveEntity(entity, "", new List<string>() { "Details" });
                    foreach (var d in entity.Details)
                    {
                        d.ID = 0;
                        d.ChooseMainID = entity.ID;
                        AccessHelper.GetInstance().SaveEntity(d, "", null);
                    }
                }
                else
                {
                    int id = AccessHelper.GetInstance().SaveEntity(entity, "", new List<string>() { "Details" });
                    foreach (var d in entity.Details)
                    {
                        d.ChooseMainID = id;
                        AccessHelper.GetInstance().SaveEntity(d, "", null);
                    }
                }
            }
            catch (Exception ex)
            {
                return false;
            }
            return true;
        }

        public string DownLoadFile(string key, string GuidName, string RealName)
        {
            string filePath = System.Web.Configuration.WebConfigurationManager.AppSettings[key].ToString() + @"\" + GuidName;
            string saveFolder = System.Web.Hosting.HostingEnvironment.MapPath("~/cash" + @"\" + RealName);
            if (System.IO.File.Exists(filePath))
            {
                try
                {
                    System.IO.File.Copy(filePath, saveFolder, true);
                }
                catch (Exception ex)
                {
                    return "";
                }
                return "/cash/" + RealName;
            }


            return "";
        }

        public bool ComfirmJinShen(int MainInfoID, string Stauts)
        {
            if (Stauts == "晋升确认" && MainInfoID > 0)//确认晋升  完成一系列动作
            {
                // 简历信息加一条
                // 职务加一条  在任  前面的改为不在任
                // Chegai 信息增加一条  
                using (SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["JinYang"].ConnectionString))
                {
                    conn.Open();
                    var trancation = conn.BeginTransaction();
                    try
                    {
                        #region update JinShen_MainInfo 信息
                        StringBuilder sql = new StringBuilder(string.Format("UPDATE dbo.JinShen_MainInfo SET Status = '{0}' WHERE ID ={1} ", Stauts, MainInfoID));
                        SqlCommand cmmd = conn.CreateCommand();
                        cmmd.Transaction = trancation;
                        cmmd.CommandText = sql.ToString();
                        cmmd.ExecuteNonQuery();
                        #endregion

                        #region 新增简历信息
                        sql.Clear();
                        sql.Append(string.Format(@"INSERT INTO dbo.Emp_Resume 
        ( EmpbasicID ,
          BeginDate ,
          EndDate ,
          Unit ,
          Duty ,          
          IsPeriod        )
SELECT  emp.ID , -- EmpbasicID - int
          duty.RXRZWSXSJ, -- BeginDate - datetime
          GETDATE() , -- EndDate - datetime
          org.Name , -- Unit - varchar(200)
          duty.DutyName , -- Duty - varchar(200)         
          0  -- IsPeriod - bit
          FROM dbo.Emp_BasicInfo emp
          LEFT JOIN dbo.Emp_Duty duty ON emp.ID = duty.EmpbasicID AND duty.JobStatus='在任'
          LEFT JOIN dbo.OrgForHR org ON org.ID = emp.OrgID
          LEFT JOIN dbo.JinShen_PublicShow_Detail detail ON detail.EmpID = emp.ID
          LEFT JOIN dbo.JinShen_MainInfo main ON main.ID = detail.MainID
          WHERE main.ID = {0}", MainInfoID));
                        cmmd.CommandText = sql.ToString();
                        cmmd.ExecuteNonQuery();
                        #endregion

                        #region Chegai 信息增加一条
                        sql.Clear();
                        sql.Append(string.Format(@"        
       DECLARE @EmpID INT , @NewPositionLevel  NVARCHAR(20), @OldPositionLevel NVARCHAR(20)
        SELECT  @EmpID = detail.EmpID, @NewPositionLevel = main.PositionLevel FROM dbo.JinShen_MainInfo main 
        LEFT JOIN dbo.JinShen_PublicShow_Detail detail ON detail.MainID = main.ID 
        WHERE main.ID = {0}
        SELECT @OldPositionLevel = DutyLevel FROM dbo.Emp_Duty WHERE JobStatus='在任' AND EmpbasicID= @EmpID
        IF(@NewPositionLevel != @OldPositionLevel)
        BEGIN
			 INSERT INTO CheGai
           ([EmpID]
           ,[OldZhiJi]
           ,[Status]
           ,[Demo]
           ,[RelationID],KeyID,[type],CreateDate)
     select  detail.EmpID ,main.PositionLevel, '未导出' ,'晋升工作',main.ID,
     case when (select count(cg.id) Cou from chegai cg inner join Emp_BasicInfo emp on cg.empid = emp.id
      where cg.status = '未导出' and type='变动类' and emp.orgid = 
      (select orgid from Emp_BasicInfo where id = detail.EmpID)) > 0 then
      (select top 1 keyid from chegai cg inner join Emp_BasicInfo emp on cg.empid = emp.id
      where cg.status = '未导出' and type='变动类' and emp.orgid = 
      (select orgid from Emp_BasicInfo where id = detail.EmpID)) else
      (select top 1 id + 1 from chegai order by id desc) end KeyID,
        '变动类',
        GETDATE()
     FROM dbo.JinShen_MainInfo main 
        LEFT JOIN dbo.JinShen_PublicShow_Detail detail ON detail.MainID = main.ID 
        WHERE main.ID = {0}       
        
        END", MainInfoID));
                        cmmd.CommandText = sql.ToString();
                        cmmd.ExecuteNonQuery();
                        trancation.Commit();
                        #endregion

                        #region 职务加一条  在任  前面的改为不在任
                        sql.Clear();
                        sql.Append(string.Format(@"DECLARE @empid INT
SELECT @empid = EmpID FROM dbo.JinShen_PublicShow_Detail WHERE MainID={0}
UPDATE dbo.Emp_Duty SET JobStatus='不在任' WHERE EmpbasicID= @empid
INSERT INTO dbo.Emp_Duty         ( EmpbasicID ,          DutyNo ,          DutyName ,          DutyLevel ,
          DutyAttribute ,          OKDate ,          OrganizationName ,          Sec ,          WorkCharge ,
          ServiceMode ,          IsExceptionalPromotion ,          JobStatus ,          JobDate ,
          RenQi ,          RZSYQ ,          BRZDQRQ ,          LianRen ,          RXRZWSXSJ ,
          GRStartDate         )
SELECT @empid ,          Position,         Position , 
          PositionLevel ,           PositionAttibute, 
          GETDATE() ,          OrgName , 
          '' , -- Sec - varchar(200)
          '' , -- WorkCharge - varchar(200)
          PromotType , -- ServiceMode - varchar(300)
          CASE detail.IsPoGe
          WHEN 1 THEN '是'
          WHEN 0 THEN '否' END ,           '在任' , -- JobStatus - varchar(300)
          GETDATE() , -- JobDate - datetime
          0 , -- RenQi - int
          0 , -- RZSYQ - int
          GETDATE() , -- BRZDQRQ - datetime
          '' , -- LianRen - varchar(30)
          GETDATE() , -- RXRZWSXSJ - datetime
          GETDATE()  -- GRStartDate - datetime     
        FROM dbo.JinShen_MainInfo main 
        LEFT JOIN dbo.JinShen_DetailInfo detail ON detail.MainID = main.ID AND detail.EmpBasicID = @empid
        WHERE main.ID = {0}", MainInfoID));
                        cmmd.CommandText = sql.ToString();
                        cmmd.ExecuteNonQuery();
                        #endregion
                    }
                    catch (Exception ex)
                    {
                        trancation.Rollback();
                        return false;
                    }
                }
            }
            return true;
        }
    }
}
